#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : wh_house_rental_related_specify.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-05-14 17:19 
@Desc    : 
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)
import collections
import pandas as pd
from report_system.utils import db_util
from report_system.utils import df_util
from report_system.utils.enum_util import *

data_type_dict = {1: '城市级别', 2: '服务中心级别', 3: '门店级别', 4: '线上销售级别', 5: '线下管家级别',
                  6: '房屋级别', 7: '投资人级别'}

# 数据类型  循环拼接sql  去不同纬度的数据
date_type_dict = collections.OrderedDict({2: '昨日', 1: '本月一号至截止日', 4: '本月剩余',
                                          5: '前七天', 6: '前30天', 3: '未来是十四天'})

index_dict = {'城市级别': ['城市'],
              '服务中心级别': ['服务中心'],
              '门店级别': ['门店'],
              '线上销售级别': ['线上销售'],
              '线下管家级别': ['线下管家'],
              '房屋级别': ['房屋id'],
              '投资人级别': ['投资人姓名']}

other_dict = {'城市级别': 'city_name 城市,',
              '服务中心级别': 'service_name 服务中心,',
              '门店级别': 'work_name 门店 ,',
              '线上销售级别': 'online_sale 线上销售,',
              '线下管家级别': 'offline_sale 线下管家,',
              '投资人级别': 'invest_name 投资人姓名,',
              '房屋级别': 'concat(house_id,"") 房屋id ,'}

# 给不同纬度的数据进行 设置颜色
colors = DAY_REPORT_COLOR


def city_report_sql(dt, date_type, level=LEVEL_CITY):
    """
    获取城市级别sql
    :param dt:
    :param date_type:
    :param level:
    :return:
    """
    sql = """select 
        city_name 城市, 
        online_night 在线间夜,
        rented_night 可租间夜,
        rented_night_nolong 可租间夜（不含长租）,
        no_rented_night 不可租间夜,
        ifnull(offline_rental_night,0) 不可租但租出去间夜,
        concat(round(ifnull(rented_rate,0)*100,2),'%%') 可租率,
        concat(round(ifnull(rented_rate_nolong,0)*100,2),'%%') 可租率（不含长租）,
        order_num 下单数（含未支付）,
        order_night 下单间夜（含未支付）,
        rental_night 入住间夜（含未支付）未取消,
        rental_cancel_night 入住间夜（含未支付）已取消,
        pay_order_num 下单数（已支付）,
        pay_order_night 下单间夜（已支付）,
        pay_rental_night 入住间夜（已支付）未取消,
        pay_rental_cancel_night 入住间夜（已支付）已取消,
        rental_night+rental_cancel_night  实际入住间夜,
        rental_night_nolong+rental_cancel_night  实际入住间夜（不含长租）,
        concat(round(ifnull(rental_rate,0)*100,2),'%%') 出租率,
        concat(round(ifnull(act_rate,0)*100,2),'%%') 出租率（不含长租）,
        round(ifnull(compensate_income,0)/100,2) 违约赔付收益,
        round(ifnull(long_income,0)/100,2) 长租收益,
        round(ifnull(gmv,0)/100,2) GMV,
        round(ifnull(gmv_nolong,0)/100,2) GMV（不含长租）,
        round(ifnull(gmv_pay,0)/100,2) GMV（已支付）,
        round(ifnull(revpar,0)/100,2) REVPAR,
        round(ifnull(revpar_nolong,0)/100,2) REVPAR（不含长租）,
        round(ifnull(day_price,0)/100,2) 平均售卖日价,
        round(ifnull(day_price_nolong,0)/100,2) 平均售卖日价（不含长租）

        from youjia_report.wh_house_rental_related 
        where cur_date = '{dt}'
        and `type` = {level}
        and date_type = {date_type}
        order by city_id ;
         """.format(dt=dt, date_type=date_type, level=level)
    return sql


def city_month(dt, level=LEVEL_CITY):
    """
    城市日报 本月一号至截止日
    :param dt:
    :param level:
    :return:
    """
    df = pd.read_sql_query(city_report_sql(dt, DTYPE_MONTH, level), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_CITY)
    df = df_util.df_set_first_title(df, TITLE_MONTH)
    return df


def city_yesterday(dt, level=LEVEL_CITY):
    """
    城市日报 昨日
    :param dt:
    :param level:
    :return:
    """
    df = pd.read_sql_query(city_report_sql(dt, DTYPE_YESTERDAY, level), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_CITY)
    df = df_util.df_set_first_title(df, TITLE_YESTERDAY)
    return df


def city_future_fourteen(dt, level=LEVEL_CITY):
    """
    城市日报 未来是十四天
    :param dt:
    :param level:
    :return:
    """
    df = pd.read_sql_query(city_report_sql(dt, DTYPE_FUTURE_FOURTEEN, level), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_CITY)
    df = df_util.df_set_first_title(df, TITLE_FUTURE_FOURTEEN)
    return df


def city_month_surplus(dt, level=LEVEL_CITY):
    """
    城市日报 本月剩余
    :param dt:
    :param level:
    :return:
    """
    df = pd.read_sql_query(city_report_sql(dt, DTYPE_MONTH_SURPLUS, level), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_CITY)
    df = df_util.df_set_first_title(df, TITLE_MONTH_SURPLUS)
    return df


def city_ago_seven(dt, level=LEVEL_CITY):
    """
    城市日报 前七天
    :param dt:
    :param level:
    :return:
    """
    df = pd.read_sql_query(city_report_sql(dt, DTYPE_AGO_SEVEN, level), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_CITY)
    df = df_util.df_set_first_title(df, TITLE_AGO_SEVEN)
    return df


def city_ago_thirty(dt, level=LEVEL_CITY):
    """
    城市日报 前30天
    :param dt:
    :param level:
    :return:
    """
    df = pd.read_sql_query(city_report_sql(dt, DTYPE_AGO_THIRTY, level), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_CITY)
    df = df_util.df_set_first_title(df, TITLE_AGO_THIRTY)
    return df


def service_report_sql(dt, date_type):
    """
    获取 服务中心级别 级别sql
    :param dt:
    :param date_type:
    :return:
    """
    sql = """select 
        city_name 城市, 
        service_name 服务中心, 
        online_night 在线间夜,
        rented_night 可租间夜,
        rented_night_nolong 可租间夜（不含长租）,
        no_rented_night 不可租间夜,
        ifnull(offline_rental_night,0) 不可租但租出去间夜,
        concat(round(ifnull(rented_rate,0)*100,2),'%%') 可租率,
        concat(round(ifnull(rented_rate_nolong,0)*100,2),'%%') 可租率（不含长租）,
        order_num 下单数（含未支付）,
        order_night 下单间夜（含未支付）,
        rental_night 入住间夜（含未支付）未取消,
        rental_cancel_night 入住间夜（含未支付）已取消,
        pay_order_num 下单数（已支付）,
        pay_order_night 下单间夜（已支付）,
        pay_rental_night 入住间夜（已支付）未取消,
        pay_rental_cancel_night 入住间夜（已支付）已取消,
        rental_night+rental_cancel_night  实际入住间夜,
        rental_night_nolong+rental_cancel_night  实际入住间夜（不含长租）,
        concat(round(ifnull(rental_rate,0)*100,2),'%%') 出租率,
        concat(round(ifnull(act_rate,0)*100,2),'%%') 出租率（不含长租）,
        round(ifnull(compensate_income,0)/100,2) 违约赔付收益,
        round(ifnull(long_income,0)/100,2) 长租收益,
        round(ifnull(gmv,0)/100,2) GMV,
        round(ifnull(gmv_nolong,0)/100,2) GMV（不含长租）,
        round(ifnull(gmv_pay,0)/100,2) GMV（已支付）,
        round(ifnull(revpar,0)/100,2) REVPAR,
        round(ifnull(revpar_nolong,0)/100,2) REVPAR（不含长租）,
        round(ifnull(day_price,0)/100,2) 平均售卖日价,
        round(ifnull(day_price_nolong,0)/100,2) 平均售卖日价（不含长租）
        
        from youjia_report.wh_house_rental_related 
        where cur_date = '{dt}'
        and `type` = {level}
        and date_type = {date_type}
        order by city_id ;
         """.format(dt=dt, date_type=date_type, level=LEVEL_SERVICE)
    return sql


def service_month(dt):
    """
    服务中心日报 本月一号至截止日
    :param dt:
    :return:
    """
    df = pd.read_sql_query(service_report_sql(dt, DTYPE_MONTH), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_SERVICE)
    df = df_util.df_set_first_title(df, TITLE_MONTH)
    return df


def service_yesterday(dt):
    """
    服务中心日报 昨日
    :param dt:
    :return:
    """

    df = pd.read_sql_query(service_report_sql(dt, DTYPE_YESTERDAY), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_SERVICE)
    df = df_util.df_set_first_title(df, TITLE_YESTERDAY)
    return df


def service_future_fourteen(dt):
    """
    服务中心日报 未来是十四天
    :param dt:
    :return:
    """
    df = pd.read_sql_query(service_report_sql(dt, DTYPE_FUTURE_FOURTEEN), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_SERVICE)
    df = df_util.df_set_first_title(df, TITLE_FUTURE_FOURTEEN)
    return df


def service_month_surplus(dt):
    """
    服务中心日报 本月剩余
    :param dt:
    :return:
    """
    df = pd.read_sql_query(service_report_sql(dt, DTYPE_MONTH_SURPLUS), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_SERVICE)
    df = df_util.df_set_first_title(df, TITLE_MONTH_SURPLUS)
    return df


def service_ago_seven(dt):
    """
    服务中心日报 前七天
    :param dt:
    :return:
    """
    df = pd.read_sql_query(service_report_sql(dt, DTYPE_AGO_SEVEN), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_SERVICE)
    df = df_util.df_set_first_title(df, TITLE_AGO_SEVEN)
    return df


def service_ago_thirty(dt):
    """
    服务中心日报 前30天
    :param dt:
    :return:
    """
    df = pd.read_sql_query(service_report_sql(dt, DTYPE_AGO_THIRTY), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_SERVICE)
    df = df_util.df_set_first_title(df, TITLE_AGO_THIRTY)
    return df


def work_report_sql(dt, date_type):
    """
    获取 门店级别 级别sql
    :param dt:
    :param date_type:
    :return:
    """
    sql = """select 
        city_name 城市, 
        work_name 门店, 
        online_night 在线间夜,
        rented_night 可租间夜,
        rented_night_nolong 可租间夜（不含长租）,
        no_rented_night 不可租间夜,
        ifnull(offline_rental_night,0) 不可租但租出去间夜,
        concat(round(ifnull(rented_rate,0)*100,2),'%%') 可租率,
        concat(round(ifnull(rented_rate_nolong,0)*100,2),'%%') 可租率（不含长租）,
        order_num 下单数（含未支付）,
        order_night 下单间夜（含未支付）,
        rental_night 入住间夜（含未支付）未取消,
        rental_cancel_night 入住间夜（含未支付）已取消,
        pay_order_num 下单数（已支付）,
        pay_order_night 下单间夜（已支付）,
        pay_rental_night 入住间夜（已支付）未取消,
        pay_rental_cancel_night 入住间夜（已支付）已取消,
        rental_night+rental_cancel_night  实际入住间夜,
        rental_night_nolong+rental_cancel_night  实际入住间夜（不含长租）,
        concat(round(ifnull(rental_rate,0)*100,2),'%%') 出租率,
        concat(round(ifnull(act_rate,0)*100,2),'%%') 出租率（不含长租）,
        round(ifnull(compensate_income,0)/100,2) 违约赔付收益,
        round(ifnull(long_income,0)/100,2) 长租收益,
        round(ifnull(gmv,0)/100,2) GMV,
        round(ifnull(gmv_nolong,0)/100,2) GMV（不含长租）,
        round(ifnull(gmv_pay,0)/100,2) GMV（已支付）,
        round(ifnull(revpar,0)/100,2) REVPAR,
        round(ifnull(revpar_nolong,0)/100,2) REVPAR（不含长租）,
        round(ifnull(day_price,0)/100,2) 平均售卖日价,
        round(ifnull(day_price_nolong,0)/100,2) 平均售卖日价（不含长租）
        
        from youjia_report.wh_house_rental_related 
        where cur_date = '{dt}'
        and `type` = {level}
        and date_type = {date_type}
        order by city_id ;
         """.format(dt=dt, date_type=date_type, level=LEVEL_WORK)
    return sql


def work_month(dt):
    """
    门店 日报 本月一号至截止日
    :param dt:
    :return:
    """
    df = pd.read_sql_query(work_report_sql(dt, DTYPE_MONTH), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_WORK)
    df = df_util.df_set_first_title(df, TITLE_MONTH)
    return df


def work_yesterday(dt):
    """
    门店 日报 昨日
    :param dt:
    :return:
    """
    df = pd.read_sql_query(work_report_sql(dt, DTYPE_YESTERDAY), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_WORK)
    df = df_util.df_set_first_title(df, TITLE_YESTERDAY)
    return df


def work_future_fourteen(dt):
    """
    门店 日报 未来是十四天
    :param dt:
    :return:
    """
    df = pd.read_sql_query(work_report_sql(dt, DTYPE_FUTURE_FOURTEEN), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_WORK)
    df = df_util.df_set_first_title(df, TITLE_FUTURE_FOURTEEN)
    return df


def work_month_surplus(dt):
    """
    门店 日报 本月剩余
    :param dt:
    :return:
    """
    df = pd.read_sql_query(work_report_sql(dt, DTYPE_MONTH_SURPLUS), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_WORK)
    df = df_util.df_set_first_title(df, TITLE_MONTH_SURPLUS)
    return df


def work_ago_seven(dt):
    """
    门店 日报 前七天
    :param dt:
    :return:
    """
    df = pd.read_sql_query(work_report_sql(dt, DTYPE_AGO_SEVEN), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_WORK)
    df = df_util.df_set_first_title(df, TITLE_AGO_SEVEN)
    return df


def work_ago_thirty(dt):
    """
    门店 日报 前30天
    :param dt:
    :return:
    """
    df = pd.read_sql_query(work_report_sql(dt, DTYPE_AGO_THIRTY), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_WORK)
    df = df_util.df_set_first_title(df, TITLE_AGO_THIRTY)
    return df


def online_report_sql(dt, date_type):
    """
    获取 线上销售级别 级别sql
    :param dt:
    :param date_type:
    :return:
    """
    sql = """select 
        city_name 城市, 
        -- work_name 门店, 
        -- service_name 服务中心, 
        online_sale 线上销售,
        online_night 在线间夜,
        rented_night 可租间夜,
        rented_night_nolong 可租间夜（不含长租）,
        no_rented_night 不可租间夜,
        ifnull(offline_rental_night,0) 不可租但租出去间夜,
        concat(round(ifnull(rented_rate,0)*100,2),'%%') 可租率,
        concat(round(ifnull(rented_rate_nolong,0)*100,2),'%%') 可租率（不含长租）,
        order_num 下单数（含未支付）,
        order_night 下单间夜（含未支付）,
        rental_night 入住间夜（含未支付）未取消,
        rental_cancel_night 入住间夜（含未支付）已取消,
        pay_order_num 下单数（已支付）,
        pay_order_night 下单间夜（已支付）,
        pay_rental_night 入住间夜（已支付）未取消,
        pay_rental_cancel_night 入住间夜（已支付）已取消,
        rental_night+rental_cancel_night  实际入住间夜,
        rental_night_nolong+rental_cancel_night  实际入住间夜（不含长租）,
        concat(round(ifnull(rental_rate,0)*100,2),'%%') 出租率,
        concat(round(ifnull(act_rate,0)*100,2),'%%') 出租率（不含长租）,
        
        round(ifnull(compensate_income,0)/100,2) 违约赔付收益,
        round(ifnull(long_income,0)/100,2) 长租收益,
        round(ifnull(gmv,0)/100,2) GMV,
        round(ifnull(gmv_nolong,0)/100,2) GMV（不含长租）,
        round(ifnull(gmv_pay,0)/100,2) GMV（已支付）,
        round(ifnull(revpar,0)/100,2) REVPAR,
        round(ifnull(revpar_nolong,0)/100,2) REVPAR（不含长租）,
        round(ifnull(day_price,0)/100,2) 平均售卖日价,
        round(ifnull(day_price_nolong,0)/100,2) 平均售卖日价（不含长租）
        
        from youjia_report.wh_house_rental_related 
        where cur_date = '{dt}'
        and `type` = {level}
        and date_type = {date_type}
        order by city_id ;
         """.format(dt=dt, date_type=date_type, level=LEVEL_ONLINE)
    return sql


def online_month(dt):
    """
    线上销售 日报 本月一号至截止日
    :param dt:
    :return:
    """
    df = pd.read_sql_query(online_report_sql(dt, DTYPE_MONTH), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_ONLINE)
    df = df_util.df_set_first_title(df, TITLE_MONTH)
    return df


def online_yesterday(dt):
    """
    线上销售 日报 昨日
    :param dt:
    :return:
    """
    df = pd.read_sql_query(online_report_sql(dt, DTYPE_YESTERDAY), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_ONLINE)
    df = df_util.df_set_first_title(df, TITLE_YESTERDAY)
    return df


def online_future_fourteen(dt):
    """
    线上销售 日报 未来是十四天
    :param dt:
    :return:
    """
    df = pd.read_sql_query(online_report_sql(dt, DTYPE_FUTURE_FOURTEEN), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_ONLINE)
    df = df_util.df_set_first_title(df, TITLE_FUTURE_FOURTEEN)
    return df


def online_month_surplus(dt):
    """
    线上销售 日报 本月剩余
    :param dt:
    :return:
    """
    df = pd.read_sql_query(online_report_sql(dt, DTYPE_MONTH_SURPLUS), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_ONLINE)
    df = df_util.df_set_first_title(df, TITLE_MONTH_SURPLUS)
    return df


def online_ago_seven(dt):
    """
    线上销售 日报 前七天
    :param dt:
    :return:
    """
    df = pd.read_sql_query(online_report_sql(dt, DTYPE_AGO_SEVEN), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_ONLINE)
    df = df_util.df_set_first_title(df, TITLE_AGO_SEVEN)
    return df


def online_ago_thirty(dt):
    """
    线上销售 日报 前30天
    :param dt:
    :return:
    """
    df = pd.read_sql_query(online_report_sql(dt, DTYPE_AGO_THIRTY), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_ONLINE)
    df = df_util.df_set_first_title(df, TITLE_AGO_THIRTY)
    return df


def offline_report_sql(dt, date_type):
    """
    获取 线下管家级别 级别sql
    :param dt:
    :param date_type:
    :return:
    """
    sql = """select 
        city_name 城市, 
       -- work_name 门店, 
       -- service_name 服务中心, 
        offline_sale 线下管家,
        online_night 在线间夜,
        rented_night 可租间夜,
        rented_night_nolong 可租间夜（不含长租）,
        no_rented_night 不可租间夜,
        ifnull(offline_rental_night,0) 不可租但租出去间夜,
        concat(round(ifnull(rented_rate,0)*100,2),'%%') 可租率,
        concat(round(ifnull(rented_rate_nolong,0)*100,2),'%%') 可租率（不含长租）,
        order_num 下单数（含未支付）,
        order_night 下单间夜（含未支付）,
        rental_night 入住间夜（含未支付）未取消,
        rental_cancel_night 入住间夜（含未支付）已取消,
        pay_order_num 下单数（已支付）,
        pay_order_night 下单间夜（已支付）,
        pay_rental_night 入住间夜（已支付）未取消,
        pay_rental_cancel_night 入住间夜（已支付）已取消,
        rental_night+rental_cancel_night  实际入住间夜,
        rental_night_nolong+rental_cancel_night  实际入住间夜（不含长租）,
        concat(round(ifnull(rental_rate,0)*100,2),'%%') 出租率,
        concat(round(ifnull(act_rate,0)*100,2),'%%') 出租率（不含长租）,
        
        round(ifnull(compensate_income,0)/100,2) 违约赔付收益,
        round(ifnull(long_income,0)/100,2) 长租收益,
        round(ifnull(gmv,0)/100,2) GMV,
        round(ifnull(gmv_nolong,0)/100,2) GMV（不含长租）,
        round(ifnull(gmv_pay,0)/100,2) GMV（已支付）,
        round(ifnull(revpar,0)/100,2) REVPAR,
        round(ifnull(revpar_nolong,0)/100,2) REVPAR（不含长租）,
        round(ifnull(day_price,0)/100,2) 平均售卖日价,
        round(ifnull(day_price_nolong,0)/100,2) 平均售卖日价（不含长租）
        
        from youjia_report.wh_house_rental_related 
        where cur_date = '{dt}'
        and `type` = {level}
        and date_type = {date_type}
        order by city_id ;
         """.format(dt=dt, date_type=date_type, level=LEVEL_OFFLINE)
    return sql


def offline_month(dt):
    """
    线下管家 日报 本月一号至截止日
    :param dt:
    :return:
    """
    df = pd.read_sql_query(offline_report_sql(dt, DTYPE_MONTH), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_OFFLINE)
    df = df_util.df_set_first_title(df, TITLE_MONTH)
    return df


def offline_yesterday(dt):
    """
    线下管家 日报 昨日
    :param dt:
    :return:
    """
    df = pd.read_sql_query(offline_report_sql(dt, DTYPE_YESTERDAY), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_OFFLINE)
    df = df_util.df_set_first_title(df, TITLE_YESTERDAY)
    return df


def offline_future_fourteen(dt):
    """
    线下管家 日报 未来是十四天
    :param dt:
    :return:
    """
    df = pd.read_sql_query(offline_report_sql(dt, DTYPE_FUTURE_FOURTEEN), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_OFFLINE)
    df = df_util.df_set_first_title(df, TITLE_FUTURE_FOURTEEN)
    return df


def offline_month_surplus(dt):
    """
    线下管家 日报 本月剩余
    :param dt:
    :return:
    """
    df = pd.read_sql_query(offline_report_sql(dt, DTYPE_MONTH_SURPLUS), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_OFFLINE)
    df = df_util.df_set_first_title(df, TITLE_MONTH_SURPLUS)
    return df


def offline_ago_seven(dt):
    """
    线下管家 日报 前七天
    :param dt:
    :return:
    """
    df = pd.read_sql_query(offline_report_sql(dt, DTYPE_AGO_SEVEN), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_OFFLINE)
    df = df_util.df_set_first_title(df, TITLE_AGO_SEVEN)
    return df


def offline_ago_thirty(dt):
    """
    线下管家 日报 前30天
    :param dt:
    :return:
    """
    df = pd.read_sql_query(offline_report_sql(dt, DTYPE_AGO_THIRTY), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_OFFLINE)
    df = df_util.df_set_first_title(df, TITLE_AGO_THIRTY)
    return df


def house_report_sql(dt, date_type):
    """
    获取 房屋级别 级别sql
    :param dt:
    :param date_type:
    :return:
    """
    sql = """select 
        whrr.city_name 城市, 
        concat(whrr.house_id,"") 房屋ID,
        concat(whs.lodge_id,"") 房源ID,
        whs.address 地址,
        whs.room_num 居室,
        whs.title 标题,
        whrr.work_name 门店, 
        whrr.service_name 服务中心, 
        whrr.online_sale 线上销售,
        whrr.offline_sale 线下管家,
        whrr.invest_name 投资人,
       
        whrr.online_night 在线间夜,
        whrr.rented_night 可租间夜,
        whrr.rented_night_nolong 可租间夜（不含长租）,
        whrr.no_rented_night 不可租间夜,
        ifnull(offline_rental_night,0) 不可租但租出去间夜,
        concat(round(ifnull(whrr.rented_rate,0)*100,2),'%%') 可租率,
        concat(round(ifnull(whrr.rented_rate_nolong,0)*100,2),'%%') 可租率（不含长租）,
        whrr.order_num 下单数（含未支付）,
        whrr.order_night 下单间夜（含未支付）,
        whrr.rental_night 入住间夜（含未支付）未取消,
        whrr.rental_cancel_night 入住间夜（含未支付）已取消,
        whrr.pay_order_num 下单数（已支付）,
        whrr.pay_order_night 下单间夜（已支付）,
        whrr.pay_rental_night 入住间夜（已支付）未取消,
        whrr.pay_rental_cancel_night 入住间夜（已支付）已取消,
        whrr.rental_night+whrr.rental_cancel_night  实际入住间夜,
        whrr.rental_night_nolong+whrr.rental_cancel_night  实际入住间夜（不含长租）,
        concat(round(ifnull(whrr.rental_rate,0)*100,2),'%%') 出租率,
        concat(round(ifnull(whrr.act_rate,0)*100,2),'%%') 出租率（不含长租）,
        round(ifnull(whrr.compensate_income,0)/100,2) 违约赔付收益,
        round(ifnull(whrr.long_income,0)/100,2) 长租收益,
        round(ifnull(whrr.gmv,0)/100,2) GMV,
        round(ifnull(whrr.gmv_nolong,0)/100,2) GMV（不含长租）,
        round(ifnull(whrr.gmv_pay,0)/100,2) GMV（已支付）,
        round(ifnull(whrr.revpar,0)/100,2) REVPAR,
        round(ifnull(whrr.revpar_nolong,0)/100,2) REVPAR（不含长租）,
        round(ifnull(whrr.day_price,0)/100,2) 平均售卖日价,
        round(ifnull(whrr.day_price_nolong,0)/100,2) 平均售卖日价（不含长租）,
        round(whs.month_rent/100,2) 房租租金,
        concat(round(ifnull(whrr.rent_tate,0)*100,2),'%%') 租金完成度
        
        
        from youjia_report.wh_house_static whs
        left join youjia_report.wh_house_rental_related whrr on whs.house_id = whrr.house_id
        where whrr.cur_date = '{dt}'
       
        and whrr.type = {level}
        and whrr.date_type = {date_type}
        order by whrr.city_id ;
         """.format(dt=dt, date_type=date_type, level=LEVEL_HOUSE)
    #  todo
    return sql


def house_basics():
    sql = """select concat(whs.house_id,"") 房屋ID ,
        whs.title 房屋title,
        whs.room_num 居室,
        round(whs.area/100,2) 面积,
        whs.address 地址,
         date(htn.online_time) 上线时间,
        date(htn.date_sign) 签约时间
        from youjia_report.wh_house_static whs
        left join youjia_report.wh_house_time_node htn on htn.house_id = whs.house_id
        where whs.join_type = 1
        group by whs.house_id"""
    df = pd.read_sql_query(sql, db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, '房屋ID')
    df = df_util.df_set_first_title(df, '基础信息')
    return df


def house_yesterday(dt):
    """
    房屋 日报 昨日
    :param dt:
    :return:
    """
    df = pd.read_sql_query(house_report_sql(dt, DTYPE_YESTERDAY), db_util.youjia_13_sjz_db)
    # print(df.shape)
    # df = df_util.df_set_index(df, INDEX_HOUSE)
    # df = df_util.df_set_first_title(df, TITLE_YESTERDAY)
    return df


def house_month(dt):
    """
    房屋 日报 本月一号至截止日
    :param dt:
    :return:
    """
    df = pd.read_sql_query(house_report_sql(dt, DTYPE_MONTH), db_util.youjia_13_sjz_db)
    # df = df_util.df_set_index(df, INDEX_HOUSE)
    # df = df_util.df_set_first_title(df, TITLE_MONTH)
    return df


def house_future_fourteen(dt):
    """
    房屋 日报 未来是十四天
    :param dt:
    :return:
    """
    df = pd.read_sql_query(house_report_sql(dt, DTYPE_FUTURE_FOURTEEN), db_util.youjia_13_sjz_db)
    # df = df_util.df_set_index(df, INDEX_HOUSE)
    # df = df_util.df_set_first_title(df, TITLE_FUTURE_FOURTEEN)
    return df


def house_month_surplus(dt):
    """
    房屋 日报 本月剩余
    :param dt:
    :return:
    """
    df = pd.read_sql_query(house_report_sql(dt, DTYPE_MONTH_SURPLUS), db_util.youjia_13_sjz_db)
    # df = df_util.df_set_index(df, INDEX_HOUSE)
    # df = df_util.df_set_first_title(df, TITLE_MONTH_SURPLUS)
    return df


def house_ago_seven(dt):
    """
    房屋 日报 前七天
    :param dt:
    :return:
    """
    df = pd.read_sql_query(house_report_sql(dt, DTYPE_AGO_SEVEN), db_util.youjia_13_sjz_db)
    # df = df_util.df_set_index(df, INDEX_HOUSE)
    # df = df_util.df_set_first_title(df, TITLE_AGO_SEVEN)
    return df


def house_ago_thirty(dt):
    """
    房屋 日报 前30天
    :param dt:
    :return:
    """
    df = pd.read_sql_query(house_report_sql(dt, DTYPE_AGO_THIRTY), db_util.youjia_13_sjz_db)
    # df = df_util.df_set_index(df, INDEX_HOUSE)
    # df = df_util.df_set_first_title(df, TITLE_AGO_THIRTY)
    return df


def invest_report_sql(dt, date_type):
    """
    获取 投资人级别 级别sql
    :param dt:
    :param date_type:
    :return:
    """
    sql = """select 
        city_name 房屋分布城市, 
        invest_name 投资人,
        house_id 房屋数,
        online_night 在线间夜,
        rented_night 可租间夜,
        concat(round(rented_rate*100,2),'%%') 可租率,
        no_rented_night 不可租间夜,
        rental_night+rental_cancel_night  实际入住间夜,
        concat(round(rental_rate*100,2),'%%') 出租率,
        round(ifnull(gmv - compensate_income,0)/100,2) 入住收益,
        round(ifnull(compensate_income,0)/100,2) 违约赔付收益,
        round(ifnull(gmv,0)/100,2) GMV,
        round(ifnull(revpar,0)/100,2) REVPAR,
        round(ifnull(day_price,0)/100,2) 平均售卖日价
        from youjia_report.wh_house_rental_related 
        where cur_date = '{dt}'
        and `type` = {level}
        and date_type = {date_type}
        order by city_id ;
         """.format(dt=dt, date_type=date_type, level=LEVEL_INVESTOR)
    return sql


def invest_month(dt):
    """
    投资人 日报 本月一号至截止日
    :param dt:
    :return:
    """
    df = pd.read_sql_query(invest_report_sql(dt, DTYPE_MONTH), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_INVESTOR)
    df = df_util.df_set_first_title(df, TITLE_MONTH)
    return df


def invest_yesterday(dt):
    """
    投资人 日报 昨日
    :param dt:
    :return:
    """
    df = pd.read_sql_query(invest_report_sql(dt, DTYPE_YESTERDAY), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_INVESTOR)
    df = df_util.df_set_first_title(df, TITLE_YESTERDAY)
    return df


def invest_future_fourteen(dt):
    """
    投资人 日报 未来是十四天
    :param dt:
    :return:
    """
    df = pd.read_sql_query(invest_report_sql(dt, DTYPE_FUTURE_FOURTEEN), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_INVESTOR)
    df = df_util.df_set_first_title(df, TITLE_FUTURE_FOURTEEN)
    return df


def invest_month_surplus(dt):
    """
    投资人 日报 本月剩余
    :param dt:
    :return:
    """
    df = pd.read_sql_query(invest_report_sql(dt, DTYPE_MONTH_SURPLUS), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_INVESTOR)
    df = df_util.df_set_first_title(df, TITLE_MONTH_SURPLUS)
    return df


def invest_ago_seven(dt):
    """
    投资人 日报 前七天
    :param dt:
    :return:
    """
    df = pd.read_sql_query(invest_report_sql(dt, DTYPE_AGO_SEVEN), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_INVESTOR)
    df = df_util.df_set_first_title(df, TITLE_AGO_SEVEN)
    return df


def invest_ago_thirty(dt):
    """
    投资人 日报 前30天
    :param dt:
    :return:
    """
    df = pd.read_sql_query(invest_report_sql(dt, DTYPE_AGO_THIRTY), db_util.youjia_13_sjz_db)
    df = df_util.df_set_index(df, INDEX_INVESTOR)
    df = df_util.df_set_first_title(df, TITLE_AGO_THIRTY)
    return df


def get_type_df(recdate, data_type, date_type, other_name=None):
    """

    :param recdate: 时间 wh_house_rental_related 的时间
    :param data_type: '数据级别:1 城市级别 2，服务中心级别 3门店级别 4 线上销售级别 5 线下管家级别 6 房源级别 7 投资人级别'
    :param date_type: '数据日期类型  1 本月一号至截止日  2 昨日 3 未来是十四天 4 本月剩余 5 前七天 6 前30天',
    :param other_name:
    :return:
    """
    sql = """   select 
                {other_name}
                online_night 在线间夜,
                rented_night 可租间夜,
                ifnull(offline_rental_night,0) 不可租但租出去间夜,
                concat(round(rented_rate*100,2),'%%') 可租率,
                no_rented_night 不可租间夜,
                order_num 下单数（含未支付）,
                order_night 下单间夜（含未支付）,
                rental_night 入住间夜（含未支付）未取消,
                rental_cancel_night 入住间夜（含未支付）已取消,
                pay_order_num 下单数（已支付）,
                pay_order_night 下单间夜（已支付）,
                pay_rental_night 入住间夜（已支付）未取消,
                pay_rental_cancel_night 入住间夜（已支付）已取消,
                rental_night+rental_cancel_night  实际入住间夜,
                concat(round(rental_rate*100,2),'%%') 出租率,
                round(compensate_income/100,2) 违约赔付收益,
                round(gmv/100,2) GMV,
                round(gmv_pay/100,2) GMV（已支付）,
                round(revpar/100,2) REVPAR,
                round(day_price/100,2) 平均售卖日价
                from youjia_report.wh_house_rental_related 
                where cur_date = '{recdate}'
                and `type` = {data_type}
                and date_type = {date_type}
                order by city_id ;""".format(recdate=recdate, data_type=data_type,
                                             date_type=date_type, other_name=other_name)
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)  # .astype('category')
    # return db_util.records_select_df(sql)


if __name__ == '__main__':
    """
    # todo pass
    """
    # data, colors = knead_data('2019-05-31', [4])
    #
    # from report_system.utils import excel_util
    # # excel_util.pd_to_excel(data,'房源日报')
    #
    # from report_system.utils import mail_util
    #
    # mail_util.distribute_mail(df_dict=data, file_name='线上管家', text='线上管家', html_sheet_name='城市级别', suffix='xls',
    #                           title="整合2", colors_dict=colors, receiver='dt@iyoujia.com')
    # work_month('2019-05-20')
